using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using SQLiten.Properties;
using SQLitenSupport.Data;
using SQLitenSupport.UI;

namespace SQLiten
{

    /// <summary>
    /// 
    /// </summary>
    public partial class SQLiten : Form
    {

        #region Enum(s)

        // Selected Tab indicator
        protected enum SelectedTab
        {

            Results,
            Messages,
            TableData,
            Objects,
            History

        };

        #endregion

        #region Events & Objects

        // Data Transaction Object
        private SQLTransactions DBTransactions = null;

        // Table creation object
        private CreateTable createTable = null;

        // Root node object
        private TreeNode rootNode = null;

        // Table Node
        private TreeNode tableNode = null;

        // Table Properties Nodes
        private TreeNode columnNode = null;
        private TreeNode indexNode = null;
        private TreeNode propertyNode = null;

        // Settings
        private Settings SQLitenConfig;

        #endregion

        #region Class Constants & Variables

        // Backing var for DatabaseName property
        private string _DatabaseName;

        // Tree node indicator constants
        private const string _ROOT_NODE = "ROOT";
        private const string _TABLE_NODE = "TABLE";
        private const string _COLUMN_NODE = "Columns";
        private const string _INDEX_NODE = "Indices";
        private const string _PROPERTY_NODE = "Property";

        #endregion

        #region Properties

        /// <summary>
        /// Gets/sets the current database name (with full path information)
        /// </summary>
        private string DatabaseName
        {
            get { return _DatabaseName; }
            
            set 
            { 
            
                // Change DB Name
                _DatabaseName = value;         
            
            }

        }

        #endregion

        #region Constructor(s)

        /// <summary>
        /// 
        /// </summary>
        public SQLiten()
        {

            // Init method
            InitializeComponent();

            // Other init routines
            InitializeSystem();

        }

        #endregion

        #region Initialization & UnInitialization Routines

        /// <summary>
        /// 
        /// </summary>
        private void InitializeSystem()
        {

            // Default settings
            this.SQLitenConfig = Settings.Default;

            // Load Settings
            _DatabaseName = SQLitenConfig.LastOpened;

            // Refresh Tree View
            RefreshTreeView();

            // Associate event handler with form closing/application exit
            this.FormClosing += new FormClosingEventHandler( SQLiten_FormClosing );

            // TreeView related event handlers
            treeDatabase.NodeMouseClick += new TreeNodeMouseClickEventHandler( treeDatabase_NodeMouseClick );

            // TabPage related event handlers
            flatTabData.SelectedIndexChanged += new EventHandler( flatTabData_SelectedIndexChanged );

        }

        // Form closing - save settings
        void SQLiten_FormClosing( object sender, FormClosingEventArgs e )
        {

            // Save the settings
            SaveSettings();

        }

        /// <summary>
        /// 
        /// </summary>
        private void SaveSettings()
        {

            try
            {

                // Set the options
                SQLitenConfig.LastOpened = _DatabaseName;

                // Save 
                SQLitenConfig.Save();

            } // try
            catch (Exception ex)
            {

                // Keep the compiler happy
                ex.Message.ToString();

                MessageBox.Show( "There were errors in saving the configuration data." );

            } // catch

        }

        #endregion

        #region Top Menu Click Handlers

        #region File Menu Clicks

        private void mnuCreateDB_Click( object sender, EventArgs e )
        {

            // Create Transaction objects
            DBTransactions = new SQLTransactions();

            // Set title
            saveFile.Title = "Create new SQLite Database";

            // Set init. directory
            saveFile.InitialDirectory = Application.StartupPath;

            // Show open file dialog
            saveFile.ShowDialog( this );

            // Exit if no file chosen
            if (saveFile.FileName.Length == 0)
            {

                // Dispose transaction object
                if (DBTransactions != null)
                {

                    DBTransactions.Dispose();
                    DBTransactions = null;

                }

                // Exit method
                return;

            }

            // Extract filename
            string fileName = extractFileName( saveFile.FileName );

            // Try to create the database
            if (DBTransactions.CreateDatabase( saveFile.FileName ) == SQLStatus.CreateDatabaseSuccess)
            {

                // Set database name
                DatabaseName = saveFile.FileName;

                // Create root node
                AddTreeRoot( fileName );

            } // if
            else
            {

                // Show error
                MessageBox.Show( "Database creation error." );

            } // else

            // Dispose transaction object
            if (DBTransactions != null)
            {

                DBTransactions.Dispose();
                DBTransactions = null;

            } // if

        }

        // Open database
        private void mnuOpenDB_Click( object sender, EventArgs e )
        {

            // Set title
            openFile.Title = "Open SQLite Database";

            // Set initial path
            openFile.InitialDirectory = Application.StartupPath;

            // Show as dialog
            openFile.ShowDialog( this );

            // Exit if no file chosen
            if (openFile.FileName.Length == 0)
                return;

            // Extract filename
            string fileName = extractFileName( openFile.FileName );

            // Set database name
            DatabaseName = openFile.FileName;

            // Show tree
            RefreshTreeView();

        }

        // Exit program
        private void mnuExit_Click( object sender, EventArgs e )
        {

            // Dispose myself
            this.Dispose( true );

            // Call garbage collector
            GC.Collect();

            // Exit App
            Application.Exit();

        }

        #endregion

        #region Help Menu Clicks

        // Show about box
        private void mnuAbout_Click( object sender, EventArgs e )
        {

            // Create new instance
            AboutSQLiten aboutBox = new AboutSQLiten();

            // Show box
            aboutBox.ShowDialog( this );

        }

        #endregion

        #endregion

        #region Context Menu Click Handlers

        #region Root Context-Menu Clicks

        // Create a new table dialog
        private void cmenuRootCreateTable_Click( object sender, EventArgs e )
        {

            // Init a new dialog
            createTable = new CreateTable( DatabaseName );

            // Associate event handler
            createTable.DialogDisposed += new CreateTable.DialogFinished( createTable_DialogDisposed );

            // Show dialog
            createTable.ShowDialog( this );

        }

        #endregion

        #endregion

        #region Various Interface Element Click Handlers

        private void chkShowAll_CheckedChanged( object sender, EventArgs e )
        {

            if (chkShowAll.CheckState == CheckState.Checked)
            {

                nudFrom.Enabled = false;
                nudTo.Enabled = false;

            } // if
            else
            {

                nudFrom.Enabled = true;
                nudTo.Enabled = true;

            } // else

        }

        #endregion

        #region Methods relating to TreeView

        // Method to refresh the Tree View
        private void RefreshTreeView()
        {

            // Local vars
            DataTable resultTable = null;
            DataSet resultSet = null;

            // New Transactional object
            DBTransactions = new SQLTransactions();
            
            // Connection string
            DBTransactions.ConnectionString = "Data Source=" + _DatabaseName;

            // Get tables
            resultTable = DBTransactions.GetTables();

            // Begin Update
            treeDatabase.BeginUpdate();

            // Clear tree & Add root node
            AddTreeRoot( extractFileName( DatabaseName ) );

            // Debug Info
            // ShowTableSchemaInfo(resultTable);

            // Loop and add data
            for (int tableCount = 0; tableCount < resultTable.Rows.Count; tableCount++)
            {

                // Define new Table node
                tableNode = new TreeNode();
                tableNode.Text = resultTable.Rows[tableCount][(int)SQLiteDatabaseSchema.TableName].ToString();
                tableNode.ImageIndex = 1;
                tableNode.SelectedImageIndex = 1;
                tableNode.Tag = _TABLE_NODE;

                // Fetch Schema for table
                resultSet = DBTransactions.GetSchema( tableNode.Text );

                // Define new Columns Node
                columnNode = new TreeNode();
                columnNode.Text = _COLUMN_NODE;
                columnNode.ImageIndex = 2;
                columnNode.SelectedImageIndex = 2;
                columnNode.Tag = _COLUMN_NODE;

                // Add column info
                for (int rowCount = 0; rowCount < resultSet.Tables[0].Rows.Count; rowCount++)
                {

                    propertyNode = new TreeNode();

                    propertyNode.Text =
                        resultSet.Tables[0].Rows[rowCount][(int)SQLiteTableSchema.ColumnName].ToString() + " [" +
                        resultSet.Tables[0].Rows[rowCount][(int)SQLiteTableSchema.DataType].ToString() + ", " +
                        (resultSet.Tables[0].Rows[rowCount][(int)SQLiteTableSchema.IsNullable].ToString().ToLower() == "true" ? "NULL" : "NOTNULL") + "]";

                    propertyNode.ImageIndex = 3;
                    propertyNode.SelectedImageIndex = 3;
                    propertyNode.Tag = _PROPERTY_NODE;
                    columnNode.Nodes.Add( propertyNode );

                } // for

                // Add to Table Node
                tableNode.Nodes.Add( columnNode );

                // Define new Indices Node
                indexNode = new TreeNode();
                indexNode.Text = _INDEX_NODE;
                indexNode.ImageIndex = 2;
                indexNode.SelectedImageIndex = 2;
                indexNode.Tag = _INDEX_NODE;

                // Add Index info
                for (int rowCount = 0; rowCount < resultSet.Tables[0].Rows.Count; rowCount++)
                {

                    if (resultSet.Tables[0].Rows[rowCount][(int)SQLiteTableSchema.PrimaryKey].ToString() == "true")
                    {

                        propertyNode = new TreeNode();
                        propertyNode.Text = resultSet.Tables[0].Rows[rowCount][(int)SQLiteTableSchema.ColumnName].ToString() + ", PRIMARY";
                        propertyNode.ImageIndex = 4;
                        propertyNode.SelectedImageIndex = 4;
                        propertyNode.Tag = _PROPERTY_NODE;
                        indexNode.Nodes.Add( propertyNode );
                    
                    } // if

                } // for

                // Add to Table Node
                tableNode.Nodes.Add( indexNode );

                // Add node to root
                rootNode.Nodes.Add( tableNode );

            } // for

            // End update
            treeDatabase.EndUpdate();

            // Expand
            treeDatabase.Nodes[0].Expand();

            // Dispose
            if (DBTransactions != null)
            {

                DBTransactions.Dispose();
                DBTransactions = null;

            } // if

        }
        
        // Method to Add the Root Node
        private void AddTreeRoot( string nodeName )
        {

            // Clear tree
            treeDatabase.Nodes.Clear();

            // Create new root node
            rootNode = new TreeNode();
            rootNode.Text = nodeName;
            rootNode.ImageIndex = 0;
            rootNode.SelectedImageIndex = 0;
            rootNode.Tag = _ROOT_NODE;
            rootNode.ContextMenuStrip = cmenuRoot;

            // Add database name to tree root node
            treeDatabase.Nodes.Add( rootNode );

        }

        // Tree Node is clicked
        void treeDatabase_NodeMouseClick( object sender, TreeNodeMouseClickEventArgs e )
        {

            // Determine which tab is selected and act accordingly
            switch (flatTabData.SelectedIndex)
            {

                case (int)SelectedTab.Results:
                    break;

                case (int)SelectedTab.Messages:
                    break;

                case (int)SelectedTab.TableData:
                    RefreshDataTab();                       // Refresh Table Data
                    break;

                case (int)SelectedTab.Objects:
                    break;

                case (int)SelectedTab.History:
                    break;

            } // switch

        }

        #endregion

        #region Methods relating to TabPages

        // Tab selection changed
        void flatTabData_SelectedIndexChanged( object sender, EventArgs e )
        {

            // Determine which tab is selected and act accordingly
            switch (flatTabData.SelectedIndex)
            {

                case (int)SelectedTab.Results:
                    break;

                case (int)SelectedTab.Messages:
                    break;

                case (int)SelectedTab.TableData:
                    RefreshDataTab();                       // Refresh Table Data
                    break;

                case (int)SelectedTab.Objects:
                    break;

                case (int)SelectedTab.History:
                    break;

            } // switch

        }

        // Refreshes the data tab
        private void RefreshDataTab()
        {

            // Abstract column class
            DataGridViewColumn dgvColumn = null;

            // Initial Check
            if (DatabaseName == null)
                return;

            // Exit if table isn't selected
            if (treeDatabase.SelectedNode == null)
                return;

            // No table selected - Root node, exit
            if (treeDatabase.SelectedNode.Tag.ToString() == _ROOT_NODE)
                return;

            // Local vars
            DataSet resultSet = new DataSet();
            DBTransactions = new SQLTransactions();

            // Connection String
            DBTransactions.ConnectionString = "Data Source=" + _DatabaseName;

            // Fetch data
            try
            {

                TreeNode traverser = treeDatabase.SelectedNode;

                // Traverse nodes upward till Table Node is found
                if (traverser.Tag.ToString() != _TABLE_NODE)
                {

                    do
                    {

                        traverser = traverser.Parent;


                    } while (traverser.Tag.ToString() != _TABLE_NODE);

                } // if
              
                // Get Schema for Table
                resultSet = DBTransactions.GetSchema( traverser.Text );

                // Update DataGridView
                dgvTableData.Columns.Clear();

                // Loop and add columns
                for (int rowCount = 0; rowCount < resultSet.Tables[0].Rows.Count; rowCount++)
                {

                    dgvColumn = new DataGridViewTextBoxColumn();
                    dgvColumn.HeaderText = resultSet.Tables[0].Rows[rowCount][(int)SQLiteTableSchema.ColumnName].ToString();
                    dgvTableData.Columns.Add( dgvColumn );

                } // for


            } // try
            catch (SQLException ex)
            {

                // Show error
                if (ex.ExceptionStatus == SQLStatus.TableSchemaGetFailure)
                    MessageBox.Show( "Error fetching table schema." );
                else
                    MessageBox.Show( "Unknown error while reading table schema." );

                // Exit method
                return;

            }
            finally
            {

                // Dispose transactor
                DisposeTransactor();

            }

            // Clear Rows
            dgvTableData.Rows.Clear();

            // Show Table Schema
            // ShowTableSchemaInfo( resultSet );

        }

        #endregion

        #region Dialog Dispose Traps

        // Create table disposed
        void createTable_DialogDisposed( string tableName )
        {
            
            // Refresh the Tree View
            RefreshTreeView();

        }

        #endregion

        #region Miscellaneous Helper Methods

        // Method to extract filename from full path
        private string extractFileName( string fullPath )
        {

            // Extract just filename from full path
            int count = fullPath.Length;
            while (fullPath[count - 1] != '\\')
                count--;

            // Return result
            return fullPath.Substring( count );

        }

        #endregion

        #region Debug Methods

        private void ShowDatabaseSchemaInfo( DataTable resultTable )
        {

            MessageBox.Show( resultTable.Rows[0][0].ToString() + "\n" +
                resultTable.Rows[0][1].ToString() + "\n" +
                resultTable.Rows[0][2].ToString() + "\n" +
                resultTable.Rows[0][3].ToString() + "\n" +
                resultTable.Rows[0][4].ToString() + "\n" +
                resultTable.Rows[0][5].ToString()

                );
            MessageBox.Show( resultTable.Columns[0].ColumnName + "\n" +
                resultTable.Columns[1].ColumnName + "\n" +
                resultTable.Columns[2].ColumnName + "\n" +
                resultTable.Columns[3].ColumnName + "\n" +
                resultTable.Columns[4].ColumnName + "\n" +
                resultTable.Columns[5].ColumnName

                );

        }

        private void ShowTableSchemaInfo( DataSet resultSet )
        {

            string col = "";
            for (int count = 0; count < resultSet.Tables[0].Columns.Count; count++)
                col += resultSet.Tables[0].Columns[count].ColumnName + "; ";
            col += "\n";
            for (int count = 0; count < resultSet.Tables[0].Columns.Count; count++)
                col += resultSet.Tables[0].Rows[0][count] + "; ";
            MessageBox.Show( col );

        }

        #endregion

        #region Destructor(s)

        // Method to dispose all connector objects
        private void DisposeTransactor()
        {

            // Dispose and set objects to null
            if (DBTransactions != null)
            {
                DBTransactions.Dispose(); DBTransactions = null;
            }

        }

        #endregion

 
    } // class

} // namespace